package com.hmsm.sys.daoImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.hmsm.server.base.BaseDao;
import com.hmsm.server.util.Util;
import com.hmsm.sys.dao.UserInfoDao;
import com.hmsm.sys.dto.UserDto;
import com.hmsm.sys.po.LogInfo;
import com.hmsm.sys.po.UserInfo;
import com.lzyyj.common.Page;

@Repository
public class UserInfoDaoImpl extends BaseDao<UserInfo, Integer> implements UserInfoDao {

	/**
	 * 获取所有的用户信息
	 * @param page 当前页
	 * @param rowsPage 每页数量 0表示获取所有数据
	 * @return 返回翻页的数量
	 */
	@SuppressWarnings("unchecked")
	public List<UserInfo> queryUserInfo(int page,int rowsPage){
		List<UserInfo> userArray = null;
		String hql = " from UserInfo c where c.state = 1 order by c.pkId desc ";
		if(rowsPage==0){
			userArray = this.getHibernateTemplate().find(hql);
		}else{
			userArray = this.queryPageList(hql, page, rowsPage);
		}
		return userArray;
	}

	/**
	 * 根据部门ID获取用户列表信息
	 * @param deptId
	 * @return
	 * @author 舒俊
	 */
	@Override
	public List<UserInfo> getUsersByDeptId(Integer deptId){
		String sql;
		if (deptId==null||deptId==0){
			sql = "SELECT DISTINCT u.* FROM user_info u LEFT JOIN user_org uo ON uo.USER_ID = u.PK_ID AND uo.STATE = 1 WHERE u.STATE = 1 AND u.TYPE = 0 AND (uo.DEPT_ID IS NULL OR uo.DEPT_ID = 0)";
		}else{
			sql = "SELECT DISTINCT u.* FROM user_info u LEFT JOIN user_org uo ON uo.USER_ID = u.PK_ID AND uo.STATE = 1 WHERE u.STATE = 1 AND uo.DEPT_ID = "+deptId;
		}
		return queryBySql(sql);
	}

	/**
	 * 根据部门ID获取用户列表信息
	 * @param deptId
	 * @return
	 * @author 舒俊
	 */
	@SuppressWarnings("unchecked")
	public List<UserDto> getUserInfoByDeptId(int deptId){
		String hql = " select new com.hmsm.sys.dto.UserDto(c.pkId,c.userName,c.name,c.userDept) from UserInfo c ,UserOrg c1 where c.state=1 and c.pkId=c1.userId and c1.deptId = "+deptId;
		return this.getHibernateTemplate().find(hql);
	}
	
	/**
	 * 获取用户最后一次登录时间
	 * @return
	 * @author 何杰 2013-2-20
	 */
	@SuppressWarnings("unchecked")
	public String getUserLoginTime(final int userId){
		String sql = "SELECT * FROM LOG_INFO C WHERE C.USER_ID = '"+userId+"' AND C.REMARK like '&登录成功！' ORDER BY C.PK_ID DESC LIMIT 2";
		Util u = new Util();
		Session session = getSession();
		Query queryObject =session.createSQLQuery(sql).addEntity(LogInfo.class);
		List<LogInfo> all = queryObject.list();
		session.close();
		if(all.size()==2){
			LogInfo logInfo = all.get(1);
			return u.getFormatDate(logInfo.getCreateDate(), "yyyy年MM月dd日");
		}
		return "第一次登录";
	}
	/**
	 * 根据机构ID获取机构下所有用户
	 * @return
	 * @author 何杰 2013-3-14
	 */
	@SuppressWarnings("unchecked")
	public List<UserInfo> getUserInfoByOrgId(final int orgId,final String userName,final String name){
		Object obj = getHibernateTemplate().execute(new HibernateCallback(){
			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql = "select u.* from user_info u,user_org o where u.pk_Id = o.user_id and u.state = 1 ";
				if(userName != null && !"".equals(userName)){
					sql += " and u.user_name = '"+userName+"' ";
				}
				if(name != null && !"".equals(name)){
					sql += " and u.name = '"+name+"' ";
				}
				if(orgId != 0){
					sql += " and o.org_id = "+orgId;
				}
				Connection conn = session.connection();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				List<UserInfo> users = new ArrayList<UserInfo>();
				while(rs.next()){
					UserInfo user = new UserInfo();
					user.setPkId(rs.getInt("PK_ID"));
					user.setIdType(rs.getString("ID_TYPE"));
					user.setUserName(rs.getString("USER_NAME"));
					user.setName(rs.getString("NAME"));
					user.setTelPhone(rs.getString("TEL_PHONE"));
					user.setEmail(rs.getString("EMAIL"));
					user.setAddress(rs.getString("ADDRESS"));
					user.setUserDept(rs.getString("USER_DEPT"));
					user.setState(rs.getInt("STATE"));
					users.add(user);
				}
				rs.close();
				stmt.close();
				conn.close();
				session.flush();
				session.close();
				return users;
			}
		});
		return (List<UserInfo>) obj;
	}

	public Page<UserInfo> find(final Page<UserInfo> page){
		int pageSize = (int) page.getPageSize();
		
		final String userName = page.getFindByExample().getUserName();
		final String name = page.getFindByExample().getName();
		final Set<Integer> deptIds = (Set<Integer>) page.getCondition().get("deptIds");
		Integer state = page.getFindByExample().getState();
		if(state==null){
			state=1;
		}

		String cmd = "SELECT ";
		String fieldList = "c.PK_ID,c.USER_NAME,c.STATE,c.NAME,c.CREATE_DATE,c.USER_DEPT,c.SN_CODE";
		StringBuffer conditionSb = new StringBuffer(" FROM user_info AS c where (c.type is null OR c.type <> 2) and c.state = ").append(state);
		if(userName != null && !"".equals(userName)){
			conditionSb.append(" AND c.USER_NAME like '%"+userName+"%'");
		}
		if(name != null && !"".equals(name)){
			conditionSb.append(" AND c.NAME like '%"+name+"%'");
		}
		if(deptIds != null){
			StringBuffer subConditionSb = new StringBuffer("SELECT distinct u.PK_ID FROM user_info u LEFT JOIN user_org uo ON uo.USER_ID = u.PK_ID AND uo.STATE = 1 LEFT JOIN dept_info d ON d.PK_ID = uo.DEPT_ID AND d.STATE = 1 WHERE");
			
			if(deptIds.size()==0){
				subConditionSb.append(" FALSE");
			}else if(deptIds.size()==1){
				for(Integer deptId: deptIds){
					if(deptId==null||deptId==0){
						subConditionSb.append(" ( D.PK_ID = 0 OR  D.PK_ID IS NULL)");
					}else{
						subConditionSb.append("  D.PK_ID = ").append(deptId);
					}
				}
			}else{
				subConditionSb.append(" D.PK_ID in (");
				boolean isFirst = true;
				for(Integer id:deptIds){
					if (isFirst){
						isFirst = false;
					}else{
						subConditionSb.append(",");
					}
					subConditionSb.append(id);
				}
				subConditionSb.append(")");
			}
			
			conditionSb.append(" AND c.pk_id in (").append(subConditionSb).append(")");
		}

		int totalCount = getUserInfoCount(conditionSb.toString());
		page.setTotalCount(totalCount);
		
		conditionSb.insert(0, fieldList);
		conditionSb.insert(0, cmd);
		conditionSb.append(" ORDER BY c.PK_ID DESC ");
		conditionSb.append(" LIMIT "+page.getStartIndex()+","+page.getPageSize()+" ");
		final String sql = conditionSb.toString();
		
		List<UserInfo> obj = getHibernateTemplate().execute(new HibernateCallback<List<UserInfo>>(){
			@Override
			public List<UserInfo> doInHibernate(Session session) throws HibernateException, SQLException {
				Connection conn = session.connection();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				List<UserInfo> userInfos = new ArrayList<UserInfo>();
				while(rs.next()){
					UserInfo userInfo = new UserInfo();
					userInfo.setPkId(rs.getInt("PK_ID"));
					userInfo.setState(rs.getInt("STATE"));
					userInfo.setUserName(rs.getString("USER_NAME"));
					userInfo.setName(rs.getString("NAME"));
					userInfo.setUserDept(rs.getString("USER_DEPT"));
					userInfo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
					userInfo.setSnCode(rs.getInt("SN_CODE"));
					userInfos.add(userInfo);
					
				}
				rs.close();
				stmt.close();
				conn.close();
				session.flush();
				session.close();
				return userInfos;
			}
		});
		
		page.setData(obj);
		
		return page;
	}

	
	public int getUserInfoCount(String condition){
		
		final String sql = "SELECT COUNT(*) AS count" + condition;
		
		Integer obj = getHibernateTemplate().execute(new HibernateCallback<Integer>(){
			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				Connection conn = session.connection();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				rs.next();
				int count = rs.getInt("count");
				rs.close();
				stmt.close();
				conn.close();
				session.flush();
				session.close();
				return count;
			}
		});
		if (obj != null) {
			return Integer.parseInt(obj.toString());
		}
		return 0;
	}

	/**
	 * 
	 *  @Description:绑定用户密令卡
	 *  @MethodName :bindingUserMLInfo
	 *  @Author     :yaosq
	 *  @CreateDate :2013-11-15 下午05:18:12 
	 *  @param pkId
	 *
	 */
	public void bindingUserMLInfo(int pkId,int sn) {
		 String sql="SELECT * FROM user_info where PK_ID ="+pkId; 
		 List<UserInfo> list = null;
			list= this.queryBySql(sql);
			if(list.size() > 0){
				UserInfo userInfo = new UserInfo();
				for(int i = 0;i<list.size();i++){
					userInfo = list.get(i);
					userInfo.setSnCode(sn);
				}
				this.update(userInfo);
			}
	}
	
	/**
	 * 
	 *  @Description:解除绑定用户密令卡
	 *  @MethodName :jcBinding
	 *  @Author     :yaosq
	 *  @CreateDate :2013-11-15 下午05:18:12 
	 *  @param pkId
	 *
	 */
	public void jcBinding(int pkId) {
		
		 String sql="SELECT * FROM user_info where PK_ID ="+pkId; 
		 List<UserInfo> list = null;
			list= this.queryBySql(sql);
			if(list.size() > 0){
				UserInfo userInfo = new UserInfo();
				for(int i = 0;i<list.size();i++){
					userInfo = list.get(i);
					userInfo.setSnCode(0);
				}
				this.update(userInfo);
			}
	}
}
